﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Collections.Generic;
using DBService;

namespace DAL
{
    public class DBConnector
    {
        public readonly static string ConStrModel = ConfigurationManager.AppSettings["ConnectionStringModel"];
        public DBConnector()
        {

        }
        //public static DB2Command GetCommand(string FY_DBname)
        //{
        //    DB2Connection con = new DB2Connection(string.Format(ConStrModel, FY_DBname));
        //    DB2Command cmd = new DB2Command();
        //    cmd.Connection = con;
        //    return cmd;
        //}

        public static string GetConnectString(string fydm)
        {

         // return string.Format(ConStrModel, BLL.FyDataService.GetDBNameByFYDM(fydm));
         return ApplicationClass.ConnectionString;
        }

        public static DataTable GetSqlSelectData(string selectStr, string connectStr)
        {
            DataTable dt = ApplicationClass.AppDBService.Select(selectStr, connectStr);
       
            return dt;
        }

        public static object ExcecuteScalar(string selectStr, string connectStr)
        {

            return ApplicationClass.AppDBService.ExcecuteScalar(selectStr, connectStr);
        }
        public static DataTable GetTableSchema(string fydm, string tablename)
        {
            string connectStr = GetConnectString(fydm);
            string selectStr = string.Format("select * from {0} where 1=0", tablename);
            return GetSqlSelectData(selectStr, connectStr);

        }

        public static DataTable GetPro_JAL(string ffydm, string fydm, string tsdm, string ksrq, string jsrq, string sjatype)
        {
            string db_fydm;
            string proname = "pro_jal";
            if (fydm != "0")
            {
                db_fydm = fydm;
            }
            else
            {
                db_fydm = ffydm;
            }
            switch (sjatype)
            {
                case "xs":
                    proname = "pro_xsjal";
                    break;
                case "ms":
                    proname = "pro_msjal";
                    break;
                case "xz":
                    proname = "pro_xzjal";
                    break;
                case "zx":
                    proname = "pro_zxjal";
                    break;
            }

            DataTable dt = null;
            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
            addPara(collentios, "ffydm", ffydm, ParameterDirection.Input);
            addPara(collentios, "fydm", fydm, ParameterDirection.Input);
            addPara(collentios, "tsdm", tsdm, ParameterDirection.Input);
            addPara(collentios, "ksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "jsrq", jsrq, ParameterDirection.Input);
            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
         
            if (tsdm != "0")
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showrydm";
                dt.Columns["showmc"].Expression = "showrymc";

            }
            else if (fydm != "0")
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showtsdm";
                dt.Columns["showmc"].Expression = "showtsmc";
            }
            else
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showfydm";
                dt.Columns["showmc"].Expression = "showfymc";
            }

            DataView dv = dt.DefaultView;
            dv.Sort = "JAL DESC";
            dt = dv.ToTable();
          
            return dt;
        }

        public static DataTable GetPro_JAL_FC(string fymc, string tsmc, string rymc, string ksrq, string jsrq, string lxh, string fydm, string sjatype)
        {
            DataTable dt = new DataTable();
            //string proname = "pro_jal_fc";
            //switch (sjatype)
            //{
            //    case "xs":
            //        proname = "pro_xsjal_fc";
            //        break;
            //    case "xs2":
            //        proname = "pro_xsjal_fc2";
            //        break;
            //    case "ms":
            //        proname = "pro_msjal_fc";
            //        break;
            //    case "xz":
            //        proname = "pro_xzjal_fc";
            //        break;
            //    case "zx":
            //        proname = "pro_zxjal_fc";
            //        break;
            //}

            
           
            //DB2Command cmd = DAL.DBConnector.GetCommand(BLL.FyDataService.GetDBNameByFYDM(fydm));
            //cmd.CommandType = CommandType.StoredProcedure;
            //cmd.CommandText = proname;
            //cmd.Parameters.Add("ffydm", fymc);
            //cmd.Parameters.Add("fydm", tsmc);
            //cmd.Parameters.Add("tsdm", rymc);
            //cmd.Parameters.Add("ksrq", ksrq);
            //cmd.Parameters.Add("jsrq", jsrq);
            //cmd.Parameters.Add("lxh", lxh);

            //DB2DataAdapter adt = new DB2DataAdapter(cmd);
            //adt.Fill(dt);

            //cmd.Connection.Close();
            return dt;
        }

        public static DataTable GetPro_AYFX(string ffydm, string fydm, string tsdm, string ksrq, string jsrq, string lx)
        {
            string db_fydm;
            if (fydm != "0")
            {
                db_fydm = fydm;
            }
            else
            {
                db_fydm = ffydm;
            }
            string proname =  BLL.AYFX_Service.Get_FC_Pro(lx);
          
            DataTable dt = null;
            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
            addPara(collentios, "ffydm", ffydm, ParameterDirection.Input);
            addPara(collentios, "fydm", fydm, ParameterDirection.Input);
            addPara(collentios, "tsdm", tsdm, ParameterDirection.Input);
            addPara(collentios, "ksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "jsrq", jsrq, ParameterDirection.Input);
            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
            if (tsdm != "0")
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showrydm";
                dt.Columns["showmc"].Expression = "showrymc";

            }
            else if (fydm != "0")
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showtsdm";
                dt.Columns["showmc"].Expression = "showtsmc";
            }
            else
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showfydm";
                dt.Columns["showmc"].Expression = "showfymc";
            }

            return dt;
        }


        public static DataTable GetPro_AYFX_FC(string fymc, string tsmc, string rymc, string ksrq, string jsrq, string lxh, string fydm, string lx)
        {
            string proname = BLL.AYFX_Service.GetZDY_FC_Pro(lx);
           
            DataTable dt = null;
            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
            addPara(collentios, "fymc", fymc, ParameterDirection.Input);
            addPara(collentios, "tsmc", tsmc, ParameterDirection.Input);
            addPara(collentios, "rymc", rymc, ParameterDirection.Input);
            addPara(collentios, "tjksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "tjjsrq", jsrq, ParameterDirection.Input);
            addPara(collentios, "lxh", lxh, ParameterDirection.Input);
            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
            return dt;
        }



        /// <summary>
        ///协查协控存储过程查询
        /// </summary>
        public static DataTable GetCKQK_Pro(string ffydm, string fydm, string ksrq, string jsrq)
        {
            string proname = "ckqk";
            string db_fydm;
            if (fydm != "0")
            {
                db_fydm = fydm;
            }
            else
            {
                db_fydm = ffydm;
            }
           
            DataTable dt = null;
            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
           
            addPara(collentios, "ffydm", ffydm, ParameterDirection.Input);
            addPara(collentios, "fydm", fydm, ParameterDirection.Input);
            addPara(collentios, "ksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "jsrq", jsrq, ParameterDirection.Input);


            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);

            if (fydm != "0")
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showrydm";
                dt.Columns["showmc"].Expression = "showrymc";
            }
            else
            {
                dt.Columns.Add("showdm");
                dt.Columns.Add("showmc");
                dt.Columns["showdm"].Expression = "showfydm";
                dt.Columns["showmc"].Expression = "showfymc";
            }


            return dt;
        }

        private static void addPara(DBParameterCollentions paras, string columname, object value, ParameterDirection direction)
        {
             DBParameter para =new DBService.DBParameter();
             para.Direction = direction;
             para.ColumnName = columname;
             para.value = value;
             paras.AddDBParameter(para);
        }

        /// <summary>
        ///协查协控存储过程反查查询
        /// </summary>
        public static DataTable GetCKQK_FC_Pro(string fymc, string rymc, string ksrq, string jsrq, string lxh)
        {
            string proname = "CKQK_FC";


            DataTable dt = null;
            
            //DB2Command cmd = DAL.DBConnector.GetCommand(BLL.FyDataService.GetLWXCXKDBName());
            
            DBService.DBParameterCollentions collentios=new DBService.DBParameterCollentions();
            addPara(collentios, "fydm", fymc, ParameterDirection.Input);
            addPara(collentios, "rymc", rymc, ParameterDirection.Input);
            addPara(collentios, "ksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "jsrq", jsrq, ParameterDirection.Input);
            addPara(collentios, "lxh", lxh, ParameterDirection.Input);

            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
           
            return dt;
        }

        /// <summary>
        ///执行统计存储过程查询
        /// </summary>
        public static DataTable GetZXGZTJ_Pro( string ffydm, string fydm, string cbspt, string tjksrq, string tjjsrq, string proname)
        {
           
           
           // string proname = "ZXGZTJ_PG";
            DataTable dt =null;
            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
            //if (proname != "ZXGZTJ_ZXAJLX")
            //{
            //    addPara(collentios, "fffydm", 0, ParameterDirection.Input);
            //}
            addPara(collentios, "ffydm", ffydm, ParameterDirection.Input);
            addPara(collentios, "fydm", fydm, ParameterDirection.Input);
            addPara(collentios, "cbspt", cbspt, ParameterDirection.Input);
            addPara(collentios, "tjksrq", tjksrq, ParameterDirection.Input);
            addPara(collentios, "tjjsrq", tjjsrq, ParameterDirection.Input);

            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
            return dt;
        }

        /// <summary>
        ///执行统计存储过程反查查询
        /// </summary>
        public static DataTable GetZXGZTJ_FC_Pro(string WebPageName, string fydm,string tsmc, string rymc, string ksrq, string jsrq, string lxh)
        {
            string proname = "";

            switch (WebPageName.ToLower().Trim())
            {
                case "zxgztj_pg":
                    proname = "ZXGZTJ_PG_FC";
                    break;
                case "zxgztj_bm":
                    proname = "ZXGZTJ_BM_FC";
                    break;
                case "zxgztj_cf":
                    proname = "ZXGZTJ_CF_FC";
                    break;
                case "zxgztj_dj":
                    proname = "ZXGZTJ_DJ_FC";
                    break;
                case "zxgztj_fk":
                    proname = "ZXGZTJ_FK_FC";
                    break;
                case "zxgztj_jl":
                    proname = "ZXGZTJ_JL_FC";
                    break;
                case "zxgztj_ky":
                    proname = "ZXGZTJ_KY_FC";
                    break;
                case "zxgztj_pm":
                    proname = "ZXGZTJ_PM_FC";
                    break;
                case "zxgztj_xz":
                    proname = "ZXGZTJ_XZ_FC";
                    break;
                case "zxgztj_lx":
                    proname = "ZXGZTJ_ZXAJLX_FC";
                    break;
                case "zxgztj_fzys":
                    proname = "ZXGZTJ_FZYS_FC";
                    break;
                case "zxgztj_blkf":
                    proname = "ZXGZTJ_BLKF_FC";
                    break;
            }

            DataTable dt = new DataTable();
            //DB2Command cmd = DAL.DBConnector.GetCommand(BLL.FyDataService.GetLWDBName());
            //cmd.CommandType = CommandType.StoredProcedure;
            //cmd.CommandText = proname;
            //cmd.Parameters.Add("fydm", fydm);
            //cmd.Parameters.Add("tsmc", tsmc);
            //cmd.Parameters.Add("rymc", rymc);
            //cmd.Parameters.Add("tjksrq", ksrq);
            //cmd.Parameters.Add("tjjsrq", jsrq);
            //cmd.Parameters.Add("lxh", lxh);

            //DB2DataAdapter adt = new DB2DataAdapter(cmd);
            //adt.Fill(dt);

            //cmd.Connection.Close();

            DBService.DBParameterCollentions collentios = new DBService.DBParameterCollentions();
            addPara(collentios, "fydm", fydm, ParameterDirection.Input);
            addPara(collentios, "tsmc", tsmc, ParameterDirection.Input);
            addPara(collentios, "rymc", rymc, ParameterDirection.Input);
            addPara(collentios, "tjksrq", ksrq, ParameterDirection.Input);
            addPara(collentios, "tjjsrq", jsrq, ParameterDirection.Input);
            addPara(collentios, "lxh", lxh, ParameterDirection.Input);
            dt = ApplicationClass.AppDBService.ExcecuteStoredProcedure(ApplicationClass.ConnectionString, proname, collentios);
            return dt;
        }
    }
}
